﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using FX.CC.Andon.Entity.ReportManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.ReportManage
{
    public class AchievingRateService : DbHelperBase
    {
        public int GetAchievingRateReptCount(AchievingRateQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) [count] FROM UT_PrdTask AS A    ");
            strSql.Append(" INNER JOIN (SELECT X.TaskID,SUM(ElapsedTime) AS ElapsedTime FROM(SELECT TaskID,datediff(second,StartTime,EndTime) as ElapsedTime FROM UT_TaskRec WHERE EndTime IS NOT NULL) AS X GROUP BY X.TaskID ) AS B ON A.TaskID=B.TaskID ");
            strSql.Append(" INNER JOIN UT_Flow AS X ON A.FlowID=X.FlowID ");
            strSql.Append(" INNER JOIN UT_Product AS C ON X.PrdID=C.PrdID  ");
            strSql.Append(" INNER JOIN UT_Shift AS D ON A.ShiftID=D.ShiftID ");
            strSql.Append(" INNER JOIN UT_Equipment AS E ON A.EqID=E.EqID ");
            strSql.Append(" INNER JOIN UT_Line AS F ON E.LineID=F.LineID ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" AND A.BizDate >=@SBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" AND A.BizDate <=@EBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.EqID))
            {
                strSql.Append(" and A.EqID=@EqID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" and F.LineID =@LineID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.ShiftID))
            {
                strSql.Append(" and A.ShiftID=@ShiftID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdID))
            {
                strSql.Append(" and A.PrdID =@PrdID ");
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateEnd));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.EqID))
                    {
                        Db.AddInParameter(cmd, "@EqID", qcmodel.EqID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.LineID))
                    {
                        Db.AddInParameter(cmd, "@LineID", qcmodel.LineID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.ShiftID))
                    {
                        Db.AddInParameter(cmd, "@ShiftID", qcmodel.ShiftID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.PrdID))
                    {
                        Db.AddInParameter(cmd, "@PrdID", qcmodel.PrdID);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<AchievingRateEntity> GetAchievingRateReptList(AchievingRateQcEntity qcmodel)
        {
            var list = new List<AchievingRateEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.BizDate DESC ) AS idx,A.BizDate,C.PrdCode,C.PrdName,E.EqCode,E.EqName,F.LineCode,F.LineName,D.ShiftName,A.PlanQty,A.ConfirmQty,A.PlanStartTime,A.PlanEndTime,B.ElapsedTime FROM UT_PrdTask AS A   ");
            strSql.Append(" INNER JOIN (SELECT X.TaskID,SUM(ElapsedTime) AS ElapsedTime FROM(SELECT TaskID,datediff(second,StartTime,EndTime) as ElapsedTime FROM UT_TaskRec WHERE EndTime IS NOT NULL) AS X GROUP BY X.TaskID ) AS B ON A.TaskID=B.TaskID ");
            strSql.Append(" INNER JOIN UT_Flow AS X ON A.FlowID=X.FlowID ");
            strSql.Append(" INNER JOIN UT_Product AS C ON X.PrdID=C.PrdID  ");
            strSql.Append(" INNER JOIN UT_Shift AS D ON A.ShiftID=D.ShiftID ");
            strSql.Append(" INNER JOIN UT_Equipment AS E ON A.EqID=E.EqID ");
            strSql.Append(" INNER JOIN UT_Line AS F ON E.LineID=F.LineID ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" AND A.BizDate >=@SBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" AND A.BizDate <=@EBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.EqID))
            {
                strSql.Append(" and A.EqID=@EqID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" and F.LineID =@LineID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.ShiftID))
            {
                strSql.Append(" and A.ShiftID=@ShiftID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdID))
            {
                strSql.Append(" and A.PrdID =@PrdID ");
            }
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateEnd));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.EqID))
                    {
                        Db.AddInParameter(cmd, "@EqID", qcmodel.EqID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.LineID))
                    {
                        Db.AddInParameter(cmd, "@LineID", qcmodel.LineID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.ShiftID))
                    {
                        Db.AddInParameter(cmd, "@ShiftID", qcmodel.ShiftID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.PrdID))
                    {
                        Db.AddInParameter(cmd, "@PrdID", qcmodel.PrdID);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new AchievingRateEntity();
                            if (reader["BizDate"] != DBNull.Value)
                                model.BizDate = Convert.ToDateTime(reader["BizDate"]);
                            model.PrdCode = reader["PrdCode"] == obj ? null : Convert.ToString(reader["PrdCode"]);
                            model.PrdName = reader["PrdName"] == obj ? null : Convert.ToString(reader["PrdName"]);
                            model.EqCode = reader["EqCode"] == obj ? null : Convert.ToString(reader["EqCode"]);
                            model.EqName = reader["EqName"] == obj ? null : Convert.ToString(reader["EqName"]);
                            model.LineCode = reader["LineCode"] == obj ? null : Convert.ToString(reader["LineCode"]);
                            model.LineName = reader["LineName"] == obj ? null : Convert.ToString(reader["LineName"]);
                            model.ShiftName = reader["ShiftName"] == obj ? null : Convert.ToString(reader["ShiftName"]);
                            if (reader["PlanQty"] != DBNull.Value)
                                model.PlanQty = Convert.ToInt32(reader["PlanQty"]);
                            if (reader["ConfirmQty"] != DBNull.Value)
                                model.ConfirmQty = Convert.ToInt32(reader["ConfirmQty"]);
                            if (reader["PlanStartTime"] != DBNull.Value)
                                model.PlanStartTime = Convert.ToDateTime(reader["PlanStartTime"]);
                            if (reader["PlanEndTime"] != DBNull.Value)
                                model.PlanEndTime = Convert.ToDateTime(reader["PlanEndTime"]);
                            if (reader["ElapsedTime"] != DBNull.Value)
                                model.ElapsedTime = Math.Round(Convert.ToDecimal(reader["ElapsedTime"]) / 60, 2);
                            if (model.PlanQty != 0)
                            {
                                model.Rate = Math.Round(Convert.ToDecimal(model.ConfirmQty) / Convert.ToDecimal(model.PlanQty), 2) * 100 + "%";
                            }
                            else
                            {
                                model.Rate = 0 + "%";
                            }
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public IList<AchievingRateEntity> ExportAchievingRateRept(AchievingRateQcEntity qcmodel)
        {
            var list = new List<AchievingRateEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT A.BizDate,C.PrdCode,C.PrdName,E.EqCode,E.EqName,F.LineCode,F.LineName,D.ShiftName,A.PlanQty,A.ConfirmQty,A.PlanStartTime,A.PlanEndTime,datediff(second,B.StartTime,B.EndTime) as ElapsedTime FROM UT_PrdTask AS A  ");
            strSql.Append(" INNER JOIN UT_TaskRec AS B ON A.TaskID=B.TaskID ");
            strSql.Append(" INNER JOIN UT_Flow AS X ON A.FlowID=X.FlowID ");
            strSql.Append(" INNER JOIN UT_Product AS C ON X.PrdID=C.PrdID  ");
            strSql.Append(" INNER JOIN UT_Shift AS D ON A.ShiftID=D.ShiftID ");
            strSql.Append(" INNER JOIN UT_Equipment AS E ON A.EqID=E.EqID ");
            strSql.Append(" INNER JOIN UT_Line AS F ON E.LineID=F.LineID ");
            strSql.Append(" WHERE B.EndTime IS NOT NULL ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" AND A.BizDate >=@SBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" AND A.BizDate <=@EBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.EqID))
            {
                strSql.Append(" AND A.EqID=@EqID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" AND F.LineID =@LineID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.ShiftID))
            {
                strSql.Append(" AND A.ShiftID=@ShiftID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdID))
            {
                strSql.Append(" AND A.PrdID =@PrdID ");
            }
            strSql.Append(" order by A.BizDate DESC ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateEnd));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.EqID))
                    {
                        Db.AddInParameter(cmd, "@EqID", qcmodel.EqID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.LineID))
                    {
                        Db.AddInParameter(cmd, "@LineID", qcmodel.LineID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.ShiftID))
                    {
                        Db.AddInParameter(cmd, "@ShiftID", qcmodel.ShiftID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.PrdID))
                    {
                        Db.AddInParameter(cmd, "@PrdID", qcmodel.PrdID);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new AchievingRateEntity();
                            if (reader["BizDate"] != DBNull.Value)
                                model.BizDate = Convert.ToDateTime(reader["BizDate"]);
                            model.PrdCode = reader["PrdCode"] == obj ? null : Convert.ToString(reader["PrdCode"]);
                            model.PrdName = reader["PrdName"] == obj ? null : Convert.ToString(reader["PrdName"]);
                            model.EqCode = reader["EqCode"] == obj ? null : Convert.ToString(reader["EqCode"]);
                            model.EqName = reader["EqName"] == obj ? null : Convert.ToString(reader["EqName"]);
                            model.LineCode = reader["LineCode"] == obj ? null : Convert.ToString(reader["LineCode"]);
                            model.LineName = reader["LineName"] == obj ? null : Convert.ToString(reader["LineName"]);
                            model.ShiftName = reader["ShiftName"] == obj ? null : Convert.ToString(reader["ShiftName"]);
                            if (reader["PlanQty"] != DBNull.Value)
                                model.PlanQty = Convert.ToInt32(reader["PlanQty"]);
                            if (reader["ConfirmQty"] != DBNull.Value)
                                model.ConfirmQty = Convert.ToInt32(reader["ConfirmQty"]);
                            if (reader["PlanStartTime"] != DBNull.Value)
                                model.PlanStartTime = Convert.ToDateTime(reader["PlanStartTime"]);
                            if (reader["PlanEndTime"] != DBNull.Value)
                                model.PlanEndTime = Convert.ToDateTime(reader["PlanEndTime"]);
                            if (reader["ElapsedTime"] != DBNull.Value)
                                model.ElapsedTime = Math.Round(Convert.ToDecimal(reader["ElapsedTime"]) / 60, 2);
                            if (model.PlanQty != 0)
                            {
                                model.Rate = Math.Round(Convert.ToDecimal(model.ConfirmQty) / Convert.ToDecimal(model.PlanQty), 2) * 100 + "%";
                            }
                            else
                            {
                                model.Rate = 0 + "%";
                            }
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
    }
}
